﻿using Dapper;
using OMG.Fill.Frunit.Model;
using OMG.Fill.Frunit.Model.Ent;
using OMG.Fill.Frunit.Service;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.Linq;
namespace OMG.Fill.Frunit.Services
{
    /// <summary>
    /// 填报基本信息T_Report通用操作类
    /// </summary>
    public class ReportServices : Reports
    {
        /// <summary>
        /// 填报数据读取
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public Pager<List<ReportModel>> GetPageList(ReportQuery query)
        {
            AreaServices areaService = new AreaServices();
            Pager<List<ReportModel>> obj = new Pager<List<ReportModel>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            if (!string.IsNullOrEmpty(query.FillInYear))
            {
                strWhereSql += "and FillInYear = @FillInYear ";
                paramlist.Add("FillInYear", query.FillInYear);
            }
            if (!string.IsNullOrEmpty(query.TimesType))
            {
                strWhereSql += "and TimesType = @TimesType ";
                paramlist.Add("TimesType", query.TimesType);
            }
            if (!string.IsNullOrEmpty(query.TimesCycle))
            {
                strWhereSql += "and TimesCycle = @TimesCycle ";
                paramlist.Add("TimesCycle", query.TimesCycle);
            }
            //前端默认设置
            if (!string.IsNullOrEmpty(query.ReportCode))
            {
                strWhereSql += "and ReportCode = @ReportCode ";
                paramlist.Add("ReportCode", query.ReportCode);
            }
            //前端默认设置
            if (!string.IsNullOrEmpty(query.AreaCode))
            {
                strWhereSql += "and AreaCode = @AreaCode ";
                paramlist.Add("AreaCode", query.AreaCode);
            }
            string strFromSql = "from dbo.T_Report where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = " order by TimesCycle asc ";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow " + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<ReportModel>(strSql, paramlist).ToList();
                    foreach (var item in obj.rows)
                    {
                        item.StatusName = string.IsNullOrEmpty(item.Status) ? "" : DataDict.Instance["ReportStatus"][item.Status];
                        item.FillInYearName = string.IsNullOrEmpty(item.FillInYear) ? "" : DataDict.Instance["ReportYear"][item.FillInYear];
                        item.ReportCodeName = string.IsNullOrEmpty(item.ReportCode) ? "" : DataDict.Instance["ReportTableName"][item.ReportCode];
                        item.TimesTypeName = string.IsNullOrEmpty(item.TimesType) ? "" : DataDict.Instance["ReportCycle"][item.TimesType];
                        item.AreaCodeName = string.IsNullOrEmpty(item.AreaCode) ? "" : areaService.GetByCodeModel(item.AreaCode).model.Name;
                        item.StrAddTime = item.AddTime == null ? "" : item.AddTime.Value.ToString("yyyy-MM-dd hh:ss:mm");
                        item.StrUpdateTime = item.UpdateTime == null ? "" : item.UpdateTime.Value.ToString("yyyy-MM-dd hh:ss:mm");
                    }
                    obj.total = dbCon.Query<int>(strTotal, paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取填报基本信息T_Report分页列表失败", ex);
                return obj;
            }
        }

        public ResResult<List<ReportReview>> GetReportReview(string areaCode,string FillInYear)
        {
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                ResResult<List<ReportReview>> obj = new ResResult<List<ReportReview>>();
                var paramlist = new DynamicParameters();
                string strSql = "select ReportName,sort,ISNULL(a.[0], 0) as 'ReviewFalse' ,";
                strSql += "ISNULL(a.[1], 0) as 'NotFillIn' ,ISNULL(a.[2], 0) as 'FillIn' ,";
                strSql += "ISNULL(a.[3], 0) as 'AlredayReport' ,ISNULL(a.[4], 0) as 'ReviewSuccess' ,";
                strSql += "ISNULL(a.[5], 0) as 'Lock'from(";
                strSql += "select reportName,sort,Status, COUNT(1) RS from [T_Report]";
                strSql += "where 1=1 ";
                if (!string.IsNullOrEmpty(areaCode) && areaCode != "null")
                {
                    //paramlist.Add("AreaCode", areaCode);
                    //strSql += " where AreaCode = @AreaCode ";
                    if (areaCode.Substring(2, 10).Equals("0000000000"))//省级账号
                    {
                        strSql += "And AreaCode like @AreaCode ";
                        paramlist.Add("AreaCode", "%" + areaCode.Substring(0, 2) + "%");
                    }
                    else if (areaCode.Substring(4, 8).Equals("00000000"))//州级账号
                    {
                        strSql += "And AreaCode like @AreaCode ";
                        paramlist.Add("AreaCode", "%" + areaCode.Substring(0, 4) + "%");
                    }
                    else
                    {
                        strSql += "And AreaCode = @AreaCode ";
                        paramlist.Add("AreaCode", areaCode);
                    }
                }
                if (!string.IsNullOrEmpty(FillInYear))
                {
                    string year = FillInYear.Substring(0, 4);
                    strSql += "And FillInYear = @FillInYear";
                    paramlist.Add("FillInYear", year);
                }
                strSql += " group by reportName,[Status],sort";
                strSql += " ) t";
                strSql += " pivot(max(t.RS) FOR t.[Status] IN ([0] , [1], [2], [3], [4], [5])) a order by sort";
                try
                {
                    obj.model = dbCon.Query<ReportReview>(strSql, paramlist).ToList();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "任务完成结果获取成功";
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = "任务完成结果获取失败" + ex.ToString().Trim();
                }
                dbCon.Dispose();//数据库连接释放
                return obj;
            }
        }

        /// <summary>
        /// 审核列表界面
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public Pager<List<ReportModel>> GetReviewPageList(ReportQuery query)
        {
            AreaServices areaService = new AreaServices();
            Pager<List<ReportModel>> obj = new Pager<List<ReportModel>>();
            var paramlist = new DynamicParameters();
            DictServices dicService = new DictServices();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            if (!string.IsNullOrEmpty(query.FillInYear))
            {               
                strWhereSql += "and FillInYear = @FillInYear ";
                paramlist.Add("FillInYear", query.FillInYear);
            }
            if (!string.IsNullOrEmpty(query.Status))
            {
                strWhereSql += "and Status = @Status ";
                paramlist.Add("Status", query.Status);
            }
            if (!string.IsNullOrEmpty(query.TimesType))
            {
                strWhereSql += "and TimesType = @TimesType ";
                paramlist.Add("TimesType", query.TimesType);
            }
            if (!string.IsNullOrEmpty(query.TimesCycle))
            {
                strWhereSql += "and TimesCycle = @TimesCycle ";
                paramlist.Add("TimesCycle", query.TimesCycle);
            }
            //前端默认设置
            if (!string.IsNullOrEmpty(query.ReportCode))
            {
                strWhereSql += "and ReportCode = @ReportCode ";
                paramlist.Add("ReportCode", query.ReportCode);
            }            
            //前端默认设置
            if (!string.IsNullOrEmpty(query.AreaCode))
            {

                if (query.AreaCode.Substring(2, 10).Equals("0000000000"))//省级账号
                {
                    strWhereSql += "and AreaCode like @AreaCode ";
                    paramlist.Add("AreaCode", "%" + query.AreaCode.Substring(0, 2) + "%");                  
                }
                else if (query.AreaCode.Substring(4, 8).Equals("00000000"))//州级账号
                {
                    strWhereSql += "and AreaCode like  " + " '%" + query.AreaCode.Substring(0, 4) + "%' ";
                  //  paramlist.Add("AreaCode", "%" + query.AreaCode.Substring(0, 4) + "%");                 
                }
                else
                {
                    strWhereSql += "and AreaCode = @AreaCode ";
                    paramlist.Add("AreaCode", query.AreaCode);                    
                }
            }         
                string strFromSql = "from dbo.T_Report where 1=1 " + strWhereSql;
                string strTotal = "select count(1)" + strFromSql + strWhereSql;
                string strOrderSql = "AreaCode asc,TimesCycle asc, ";//自定义完成order by语句
                string strSql = @"select * from (select *,ROW_NUMBER() over (order by " + strOrderSql + " Id asc) as RowNumber " + strFromSql + ") as t ";
                strSql += "where t.RowNumber between  @minRow and @maxRow ";
                paramlist.Add("minRow", query.MinRow);
                paramlist.Add("maxRow", query.MaxRow);
                try
                {
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                        obj.rows = dbCon.Query<ReportModel>(strSql, paramlist).ToList();
                        foreach (var item in obj.rows)
                        {
                            item.StatusName = string.IsNullOrEmpty(item.Status) ? "" : DataDict.Instance["ReportStatus"][item.Status];
                            item.FillInYearName = string.IsNullOrEmpty(item.FillInYear) ? "" : DataDict.Instance["ReportYear"][item.FillInYear];
                            item.ReportCodeName = string.IsNullOrEmpty(item.ReportCode) ? "" : DataDict.Instance["ReportTableName"][item.ReportCode];
                            item.TimesTypeName = string.IsNullOrEmpty(item.TimesType) ? "" : DataDict.Instance["ReportCycle"][item.TimesType];
                            item.AreaCodeName = string.IsNullOrEmpty(item.AreaCode) ? "" : areaService.GetByCodeModel(item.AreaCode).model.Name;
                            item.StrAddTime = item.AddTime == null ? "" : item.AddTime.Value.ToString("yyyy-MM-dd hh:ss:mm");
                            item.StrUpdateTime = item.UpdateTime == null ? "" : item.UpdateTime.Value.ToString("yyyy-MM-dd hh:ss:mm");
                        }
                        obj.total = dbCon.Query<int>(strTotal, paramlist).FirstOrDefault();
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "获取成功！";
                        dbCon.Dispose();//数据库连接释放
                        return obj;
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("获取填报基本信息T_Report分页列表失败", ex);
                    return obj;
                }                      
        }
        /// <summary>
        /// 获取本年和上一年的数据
        /// </summary>
        /// <param name="nowModel"></param>
        /// <returns></returns>
        public ReportDataModel GetModel(VegNowModel nowModel)
        {
            ReportDataModel model = new ReportDataModel();
            switch (nowModel.ReportCode)
            {
                //蔬菜技术推广机构及人员情况统计表
                case "FruTecPromotion":
                    FruTecPromotionServices vegTecPromotionServices = new FruTecPromotionServices();
                    model.list = vegTecPromotionServices.GetModel(nowModel);
                    break;
                //蔬菜外销情况调查表
                case "FruExport":
                    FruExportServices vegExportServices = new FruExportServices();
                    model.list = vegExportServices.GetModel(nowModel);
                    break;
                //蔬菜生产经营实体情况表
                case "FruProsecution":
                    FruProsecutionServices vegProsecutionServices = new FruProsecutionServices();
                    model.list = vegProsecutionServices.GetModel(nowModel);
                    break;                
            }
            return model;
        }
        public ResResult<int> UpStatus(string id, string reportType, string strMsg)
        {
            strMsg = string.IsNullOrEmpty(strMsg) ? "" : strMsg;
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"UPDATE dbo.T_Report set ";
            strSql += "Status = @Status, ";
            strSql += "Opinion = @Opinion ";
            strSql += " where Id = @Id";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    int result = dbCon.Execute(strSql, new { Id = id, Status = reportType, Opinion = strMsg });
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "更新成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "更新失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("更新填报基本信息T_Report对象信息失败", ex);
                return obj;
            }
        }

        public ReportDataModel GetReportData(string reportId, string reportCode)
        {
            ReportDataModel model = new ReportDataModel();
            switch (reportCode)
            {
                //主要蔬菜生产情况调查表
                case "FruProSurvey":
                    FruProSurveyServices fruProSurveyServices = new FruProSurveyServices();
                    model.ReportId = reportId;
                    //model.list = fruProSurveyServices.GetInfo(reportId);
                    model.list = fruProSurveyServices.GetDataInfo(reportId);
                    break;
                //蔬菜外销情况调查表
                case "FruExport":
                    FruExportServices fruExportServices = new FruExportServices();
                    model.ReportId = reportId;
                    model.list = fruExportServices.GetModel(reportId);
                    break;
                //蔬菜生产经营实体情况表
                case "FruProsecution":
                    FruProsecutionServices fruProsecutionServices = new FruProsecutionServices();
                    model.ReportId = reportId;
                    model.list = fruProsecutionServices.GetModel(reportId);
                    break;
                ////蔬菜技术推广机构及人员情况统计表
                case "FruTecPromotion":
                    FruTecPromotionServices fruTecPromotionServices = new FruTecPromotionServices();
                    model.ReportId = reportId;
                    model.list = fruTecPromotionServices.GetModel(reportId);
                    break;
                ////主要水果生产情况季度统计表
                case "FruProSeason":
                    FruProSeasonServices fruProSeasonServices = new FruProSeasonServices();
                    model.ReportId = reportId;
                    model.list = fruProSeasonServices.GetModel(reportId);
                    break;

            }
            return model;
        }
    }
}